Visual Basic 4 Database How-To/Visual Basic 4 Database - How-to (The Waite Group)(1995).iso/maketabl.fr_/maketabl.fr

Text File | 1995-07-04 | 6KB | 216 lines

VERSION 4.00
Begin VB.Form Form1
   BackColor       =   &H00C0C0C0&
   Caption         =   "Table Maker"
   ClientHeight    =   5340
   ClientLeft      =   2025
   ClientTop       =   1560
   ClientWidth     =   7125
   BeginProperty Font
      name            =   "MS Sans Serif"
      charset         =   0
      weight          =   700
      size            =   8.25
      underline       =   0   'False
      italic          =   0   'False
      strikethrough   =   0   'False
   EndProperty
   Height          =   5745
   Left            =   1965
   LinkTopic       =   "Form1"
   ScaleHeight     =   5340
   ScaleWidth      =   7125
   Top             =   1215
   Width           =   7245
   Begin VB.ListBox lstData
      Height          =   3735
      Left            =   480
      Sorted          =   -1  'True
      TabIndex        =   3
      Top             =   420
      Width           =   6135
   End
   Begin VB.CommandButton cmdClose
      Cancel          =   -1  'True
      Caption         =   "Close"
      Height          =   615
      Left            =   4980
      TabIndex        =   2
      Top             =   4440
      Width           =   1635
   End
   Begin VB.CommandButton cmdDropTable
      Caption         =   "D&rop Table"
      Height          =   615
      Left            =   2760
      TabIndex        =   1
      Top             =   4440
      Width           =   1635
   End
   Begin VB.CommandButton cmdCreateTable
      Caption         =   "&Create Table"
      Height          =   615
      Left            =   480
      TabIndex        =   0
      Top             =   4440
      Width           =   1635
   End
End
Attribute VB_Name = "Form1"
Attribute VB_Creatable = False
Attribute VB_Exposed = False
Option Explicit

Private Sub Form_Load()
    Dim db As DATABASE
    Dim dbName As String
    Dim td As TableDef
    Dim tableFound As Boolean

    On Error GoTo LoadError

    tableFound = False

    ' Get the database name and open the database.
    dbName = BiblioPath()       ' BiblioPath is a function in READINI.BAS
    Set db = DBEngine.Workspaces(0).OpenDatabase(dbName)

    ' Cycle through the table definitions in DATABASE_NAME. If a table named
    ' Publisher Titles is found, initialize the Table command buttons and
    ' fill the list box with the data in the table.
    For Each td In db.TableDefs
        If td.Name = "Publisher Titles" Then
            tableFound = True
            cmdDropTable.Enabled = True
            cmdCreateTable.Enabled = False
            FillList
            Exit For
        End If
    Next

    ' The table does not exist, so set the command buttons.
    If tableFound = False Then
        cmdDropTable.Enabled = False
        cmdCreateTable.Enabled = True
    End If

Exit Sub

LoadError:
    MsgBox Error$, vbExclamation
    Unload Me
Exit Sub

End Sub

Sub FillList()
    Dim db As DATABASE
    Dim dbName As String
    Dim rs As Recordset
    Dim sql As String

    On Error GoTo FillListError
    lstData.Clear

    ' Get the database name and open the database.
    dbName = BiblioPath()       ' BiblioPath is a function in READINI.BAS
    Set db = DBEngine.Workspaces(0).OpenDatabase(dbName)

    Set rs = db.OpenRecordset("SELECT * FROM [Publisher Titles]", dbOpenSnapshot)
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        Do
            lstData.AddItem rs![Name] & ": " & rs![Title]
            rs.MoveNext
        Loop While Not rs.EOF
    End If

Exit Sub

FillListError:
    MsgBox Error$, vbExclamation
Exit Sub

End Sub

Private Sub cmdCreateTable_Click()
    Dim db As DATABASE
    Dim dbName As String
    Dim sql As String

    On Error GoTo CreateTableError

    Screen.MousePointer = 11

    ' Get the database name and open the database.
    dbName = BiblioPath()       ' BiblioPath is a function in READINI.BAS
    Set db = DBEngine.Workspaces(0).OpenDatabase(dbName)

    ' Build the SQL statement.
    ' The SELECT clause names the fields from the source recordset to be
    ' created in the new table.
    sql = "SELECT Publishers.Name, Titles.Title"

    ' The INTO clause names the new table.
    sql = sql & " INTO [Publisher Titles]"

    ' The FROM clause names the table or tables to be used as the source
    ' of the data for the new table.
    sql = sql & " FROM Publishers INNER JOIN Titles"
    sql = sql & " ON Publishers.PubID = Titles.PubID"

    ' Create the new table by executing the SQL statement.
    db.Execute (sql)

    ' Fill the list box with records.
    FillList

    ' Set the command buttons.
    cmdCreateTable.Enabled = False
    cmdDropTable.Enabled = True

    Screen.MousePointer = 0

Exit Sub

CreateTableError:
    Screen.MousePointer = 0
    MsgBox Error$, vbExclamation
Exit Sub

End Sub


Private Sub cmdDropTable_Click()
    Dim db As DATABASE
    Dim dbName As String

    On Error GoTo DropTableError

    ' Get the database name and open the database.
    dbName = BiblioPath()       ' BiblioPath is a function in READINI.BAS
    Set db = DBEngine.Workspaces(0).OpenDatabase(dbName)

    ' Delete the table and set the command buttons.
    db.Execute ("DROP TABLE [Publisher Titles]")
    cmdDropTable.Enabled = False
    cmdCreateTable.Enabled = True

    ' Clear the list box.
    lstData.Clear

Exit Sub

DropTableError:
    MsgBox Error$, vbExclamation
Exit Sub

End Sub


Private Sub cmdClose_Click()
    End
End Sub